package com.student.dao.impl;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.springframework.stereotype.Component;

import com.student.dao.CollegeRegisterPaymentDao;
import com.student.model.Area;
import com.student.model.Colleges;
import com.student.model.Profession;
import com.student.model.Subjects;

@Component("collegeRegisterPaymentDao")
public class CollegeRegisterPaymentDaoImpl extends HibernateDaoImpl implements CollegeRegisterPaymentDao {

	public Colleges getCollege(Long collegeId) throws Exception {
		String hql = "From Colleges c where c.collegeId='" + collegeId + "'";
		List<Colleges> list = this.find(hql);
		return list.get(0);
	}
	
	public List<Colleges> getAllCollege() throws Exception {
		List<Colleges> colleges = this.find("FROM Colleges");
		return colleges;
	}

	public Integer getStudentCount(Long collegesId, Long professionId, Boolean isRegister,
			Boolean sex, String subjects, String area) throws Exception {
		try {
			String hql = "SELECT COUNT(*) FROM Student s WHERE 1=1";
			if (collegesId != null) {
				hql += " AND s.profession.colleges.collegeId='" + collegesId
						+ "'";
			}
			if (professionId != null) {
				hql += " AND s.profession.professionId='" + professionId + "'";
			}
			if (isRegister != null) {
				hql += " AND s.isRegister='" + isRegister + "'";
			}
			if (sex != null) {
				hql += " AND s.sex='" + sex + "'";
			}
			if (subjects != null) {
				hql += " AND s.subjects.subjectsName='" + subjects + "'";
			}
			if (area != null) {
				hql += " AND s.area.areaName='" + area + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Integer(query.iterate().next() + "");
			}
			return 0;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public Double getStudentDoubleCount(Long collegesId, Long professionId, Boolean isRegister,
			Boolean sex, String subjects, String area) throws Exception {
		try {
			String hql = "SELECT COUNT(*) FROM Student s WHERE 1=1";
			if (collegesId != null) {
				hql += " AND s.profession.colleges.collegeId='" + collegesId
						+ "'";
			}
			if (professionId != null) {
				hql += " AND s.profession.professionId='" + professionId + "'";
			}
			if (isRegister != null) {
				hql += " AND s.isRegister='" + isRegister + "'";
			}
			if (sex != null) {
				hql += " AND s.sex='" + sex + "'";
			}
			if (subjects != null) {
				hql += " AND s.subjects.subjectsName='" + subjects + "'";
			}
			if (area != null) {
				hql += " AND s.area.areaName='" + area + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Double(query.iterate().next() + "");
			}
			return new Double(0);
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}
	public List<Profession> getAllProfession(Long collegesId) throws Exception {
		String hql = "FROM Profession p where p.colleges.collegeId='" + collegesId + "'";
		return this.find(hql);
	}

	public Double getTotalPaymentAmount (Long collegeId, Long professionId, Long studentId, Boolean isRegister)
			throws Exception {
		try {
			String hql = "SELECT SUM(p.feePayable) FROM Payment p WHERE 1=1";
			if(collegeId != null) {
				hql += " AND p.student.profession.colleges.collegeId='" + collegeId + "'";
			}
			if(professionId != null) {
				hql += " AND p.student.profession.professionId='" + professionId + "'";
			}
			if(studentId != null) {
				hql += " AND p.student.studentId='" + studentId + "'";
			}
			if(isRegister != null) {
				hql += " AND p.student.isRegister='" + isRegister + "'";
			}
			Query query = getSession().createQuery(hql);
			if(query.iterate().next() != null) {
				return new Double(query.iterate().next() + "");
			}
			return 0.0;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public Double getTotalPaidAmount(Long collegeId, Long professionId,
			Long studentId, Boolean isRegister) throws Exception {
try {
			String hql = "SELECT SUM(p.feePayable-p.unpaidFees) FROM Payment p WHERE 1=1";
			if (collegeId != null) {
				hql += " AND p.student.profession.colleges.collegeId='"
						+ collegeId + "'";
			}
			if (professionId != null) {
				hql += " AND p.student.profession.professionId='"
						+ professionId + "'";
			}
			if (studentId != null) {
				hql += " AND p.student.studentId='" + studentId + "'";
			}
			if (isRegister != null) {
				hql += " AND p.student.isRegister='" + isRegister + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Double(query.iterate().next() + "");
			}
			return 0.0;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public List<Area> getAllArea() throws Exception {
		try {
				String hql = "FROM Area";
				List<Area> list = this.find(hql);
				return list;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public List<Subjects> getAllSubject() throws Exception {
		try {
			String hql = "FROM Subjects";
			List<Subjects> list = this.find(hql);
			return list;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

}
